package com.ericsson.smart;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.ericsson.smart.dao.UtilityDAOInterface;
@Component
public class UtilityDimensionDAO implements UtilityDAOInterface {

	//static EntityManagerFactory entityManagerFactory = Persistence
		//	.createEntityManagerFactory("hello");

	//static EntityManager entityManager = entityManagerFactory.createEntityManager();

	//public UtilityDimensionDAO() {
		// if(entityManagerFactory == null) {
		// entityManagerFactory = Persistence.createEntityManagerFactory("hello");
		// entityManager = entityManagerFactory.createEntityManager();
		// }
	//}
	@PersistenceContext
	EntityManager entityManager;
	
	public EntityManager getEntityManager() {
		return entityManager;
	}

	
	public void setEntityManager(EntityManager entityManager) {
		this.entityManager = entityManager;
	}
	@Transactional(propagation=Propagation.REQUIRED)
	@SuppressWarnings("unchecked")
	public <T> List<T> getHierarchy(String tableName, String columnName) {
		String queryStr = "SELECT NAME FROM " + tableName + " ORDER BY " + columnName;
		// System.out.println("QUERY-1 : " + queryStr);
		Query resultSet = entityManager.createNativeQuery(queryStr);
		List<T> genericResultList = new ArrayList<T>();
		for (Object obj : resultSet.getResultList())
			genericResultList.add((T) obj);
		return genericResultList;
	}
	@SuppressWarnings("unchecked")
	public <T> List<T> getDistinctList(String tableName, String columnName, Class<T> genericClass) {
		String queryStr = "SELECT DISTINCT " + columnName.trim().toUpperCase() + " FROM "
				+ tableName.trim().toUpperCase() + "";
		// System.out.println("QUERY-2 : " + queryStr);
		Query resultSet = entityManager.createNativeQuery(queryStr);
		List<T> genericResultList = new ArrayList<T>();
		for (Object obj : resultSet.getResultList())
			genericResultList.add((T) obj);
		return genericResultList;
	}

	@SuppressWarnings("unchecked")
	public <T> List<T> getDynamicDistinctList(String tableName, String columnName,
			Class<T> genericClass, Map<String, Object> columnMap, boolean distinctRequired) {
		System.out.println("Inside getDynamicDistinctList() method...");
		StringBuilder queryBuilder = new StringBuilder();
		if (distinctRequired) {
			queryBuilder.append("SELECT DISTINCT TBL.").append(columnName).append(" FROM ")
					.append(tableName).append(" TBL ");
		} else {
			queryBuilder.append("SELECT TBL.").append(columnName).append(" FROM ")
					.append(tableName).append(" TBL ");
		}

		if (columnMap != null) {
			queryBuilder.append("WHERE ");
			int i = 0;
			for (Entry<String, Object> e : columnMap.entrySet()) {
				if ((e.getValue() instanceof String) || (e.getValue() instanceof Date))
					queryBuilder.append("TBL." + e.getKey() + "='" + e.getValue() + "'");
				else
					queryBuilder.append("TBL." + e.getKey() + "=" + e.getValue() + "");

				if (i < columnMap.size() - 1) {
					queryBuilder.append(" AND ");
				}
				i++;
			}
		}
		queryBuilder.append(" ORDER BY TBL.").append(columnName);

		System.out.println("QUERY-3 : " + queryBuilder.toString());
		Query resultSet = entityManager.createNativeQuery(queryBuilder.toString());
		// System.out.println("aaa-"+queryBuilder.toString());
		List<T> list = new ArrayList<T>();
		for (Object obj : resultSet.getResultList()) {
			// System.out.println("aaa-"+columnName+":"+obj);
			list.add((T) obj);
		}
		return list;
	}

	// Calling function should ensure all the relevant columns are present in table or not
	@SuppressWarnings("unchecked")
	public <T> Map<String, String> getResultData(String tableName, List<String> outputColumnList,
			Map<String, Object> equalCriteriaMap, Map<String, List<T>> inCriteriaMap,
			boolean distinctRequired) {
		System.out.println("Inside getResultData() data.....");
		StringBuilder queryBuilder = new StringBuilder();

		queryBuilder.append("SELECT ");
		if (distinctRequired) {
			queryBuilder.append("DISTINCT ");
		}

		// ??? - Though it can set multiple column but as during output only 1st & 2nd column will
		// be considered ???
		if (outputColumnList != null) {
			for (int colSeq = 0; colSeq < outputColumnList.size(); colSeq++) {
				queryBuilder.append("TBL." + outputColumnList.get(colSeq).trim().toUpperCase()
						+ " ");
				if (colSeq != outputColumnList.size() - 1) {
					queryBuilder.append(",");
				}
			}
		}
		queryBuilder.append("FROM " + tableName.trim().toUpperCase() + " " + "TBL ");

		if (equalCriteriaMap != null || inCriteriaMap != null) {
			queryBuilder.append("WHERE ");
		}

		if (equalCriteriaMap != null) {
			int conditionCount = 0;
			for (Entry<String, Object> condition : equalCriteriaMap.entrySet()) {
				if ((condition.getValue() instanceof String)
						|| (condition.getValue() instanceof Date)) {
					queryBuilder.append("TBL." + condition.getKey().trim().toUpperCase() + "='"
							+ condition.getValue() + "' ");
				} else {
					queryBuilder.append("TBL." + condition.getKey().trim().toUpperCase() + "="
							+ condition.getValue() + " ");
				}
				if (conditionCount++ < equalCriteriaMap.size() - 1) {
					queryBuilder.append("AND ");
				}
			}
		}

		T condition;
		System.out.println("Inside T condition:....");
		if (inCriteriaMap != null) {
			for (Entry<String, List<T>> inCriteria : inCriteriaMap.entrySet()) {
				if (equalCriteriaMap != null) {
					if (equalCriteriaMap.size() >= 1) {
						queryBuilder.append("AND ");
					}
				}
				// queryBuilder.append("AND ");
				if (inCriteria != null) {
					queryBuilder.append("TBL." + inCriteria.getKey() + " IN (");
					for (int inCounter = 0; inCounter < inCriteria.getValue().size(); inCounter++) {
						condition = inCriteria.getValue().get(inCounter);
						if ((condition instanceof String) || (condition instanceof Date)) {
							queryBuilder.append("'" + condition + "' ");
						} else {
							queryBuilder.append(condition + " ");
						}
						if (inCounter < inCriteria.getValue().size() - 1) {
							queryBuilder.append(", ");
						}
					}
					queryBuilder.append(") ");
				}
			}
		}
		System.out.println("QUERY-4 : " + queryBuilder.toString());

		// TypedQuery<ResultDTO> resultSet = (TypedQuery<ResultDTO>) entityManager
		// .createNativeQuery(queryBuilder.toString());
		// ResultDTO dto = null;
		//
		// List<ResultDTO> genericResultList = resultSet.getResultList();
		// for (int i = 0; i < genericResultList.size(); i++) {
		// dto = genericResultList.get(i);
		// }

		Map<String, String> resultMap = null;
		Query resultSet = entityManager.createNativeQuery(queryBuilder.toString());

		// / ??? - Only 1st and 2nd output column are processed as output ???
		List<Object[]> resultSetList = resultSet.getResultList();
		if (resultSetList != null) {
			resultMap = new HashMap<String, String>();
			for (Object[] e : resultSetList) {
				if (e.length > 1) {
					resultMap.put(e[0].toString(), e[1].toString());
				} else {
					resultMap.put(e[0].toString(), "");
				}
			}
		}
		// System.out.println("Extracted-Data: " + resultMap);
		return resultMap;
		// for (Object[] e : resultSetList) {
		// System.out.println("");
		// for(int i=0;i<outputColumnList.size();i++) {
		// System.out.print(e[i]+" ||");
		// }

		// ??? - Ideally the return should be a Map(x)<String-KEY,<Map(x)>> where String-KEY will be
		// the group by column ???
		// As of now return only two rows so sending as Map<String,String>
		// ??? - Design Alternative: A List can be returned a output with column values separate
		// by ,
	}

	// Calling function should ensure all the relevant columns are present in table or not
	@SuppressWarnings("unchecked")
	public <T> Map<String, String> getDataFromFactTable(String tableName,
			String aggregateOperation, String aggregateColumn, List<String> outputColumnList,
			Map<String, Object> equalCriteriaMap, Map<String, List<T>> inCriteriaMap,
			List<String> groupByColumnList, boolean distinctRequired) {
		System.out.println("Inside getDataFromFactTable() method..");
		StringBuilder queryBuilder = new StringBuilder();
		if (outputColumnList == null) {
			System.out
					.println("If no OutputColumn is not specified then the DATA FETCHED can't have any significance for reporting purpose");
			return null;
		}
		if (tableName == null) {
			System.out.println("No FACT Tablename specified");
			return null;
		}
		if (aggregateOperation == null) {
			System.out.println("No Aggergation Level specified");
			return null;
		}
		if (aggregateColumn == null) {
			System.out.println("No Aggergation column name specified");
			return null;
		}
		if (groupByColumnList == null) {
			System.out
					.println("No GROUP BY column specified. Performing aggregation Level on 1st column specified as outputColumnList: "
							+ outputColumnList.get(0));
			return null;
		}

		boolean additionalColumnSelected = false;
		queryBuilder.append("SELECT ");
		if (distinctRequired) {
			queryBuilder.append("DISTINCT ");
		}
		// ??? - Though it can set multiple column but as during output only 1st & 2nd column will
		// be considered ???
		if (outputColumnList != null) {
			for (int colSeq = 0; colSeq < outputColumnList.size(); colSeq++) {
				if (groupByColumnList != null) {
					if (groupByColumnList.contains(outputColumnList.get(colSeq).trim())) {
						additionalColumnSelected = true;
						queryBuilder.append("TBL."
								+ outputColumnList.get(colSeq).trim().toUpperCase() + " ");
						if (colSeq != outputColumnList.size() - 1) {
							queryBuilder.append(",");
						}
					} else {
						System.out
								.println("With Aggregation value, The additional column to be displayed: "
										+ outputColumnList.get(colSeq).trim()
										+ " :MUST be included in the column used for GROUP BY");
						return null;
					}
				} else {
					additionalColumnSelected = true;
					queryBuilder.append("TBL." + outputColumnList.get(colSeq).trim().toUpperCase()
							+ " ");
					if (colSeq != outputColumnList.size() - 1) {
						queryBuilder.append(",");
					}
				}
			}
		}
		if (additionalColumnSelected) {
			queryBuilder.append(",");
		}
		queryBuilder.append(aggregateOperation.trim().toUpperCase()).append("(")
				.append(aggregateColumn.trim().toUpperCase()).append(") ");
		queryBuilder.append("FROM " + tableName.trim().toUpperCase() + " " + "TBL ");

		if (equalCriteriaMap != null || inCriteriaMap != null) {
			queryBuilder.append("WHERE ");
		}

		if (equalCriteriaMap != null) {
			int conditionCount = 0;
			for (Entry<String, Object> condition : equalCriteriaMap.entrySet()) {
				if ((condition.getValue() instanceof String)
						|| (condition.getValue() instanceof Date)) {
					queryBuilder.append("TBL." + condition.getKey().trim().toUpperCase() + "='"
							+ condition.getValue() + "' ");
				} else {
					queryBuilder.append("TBL." + condition.getKey().trim().toUpperCase() + "="
							+ condition.getValue() + " ");
				}
				if (conditionCount++ < equalCriteriaMap.size() - 1) {
					queryBuilder.append("AND ");
				}
			}
		}
		T condition;
		System.out.println("Inside T condition:....");
		boolean isFirstCriteria = true;
		if (inCriteriaMap != null) {
			for (Entry<String, List<T>> inCriteria : inCriteriaMap.entrySet()) {
				System.out.println("AAA-ID: " + inCriteria.getKey() + " :FirstCriteria: "
						+ isFirstCriteria + " :Criteria: " + inCriteria.getValue());
				// if (!isFirstCriteria) {
				// queryBuilder.append("AND ");
				// }
				// if (inCriteria != null) {
				if (inCriteria.getValue() != null) {
					if (!isFirstCriteria) {
						queryBuilder.append("AND ");
					}
					isFirstCriteria = false;
					queryBuilder.append("TBL." + inCriteria.getKey() + " IN (");
					for (int inCounter = 0; inCounter < inCriteria.getValue().size(); inCounter++) {
						condition = inCriteria.getValue().get(inCounter);
						if ((condition instanceof String) || (condition instanceof Date)) {
							queryBuilder.append("'" + condition + "' ");
						} else {
							queryBuilder.append(condition + " ");
						}
						if (inCounter < inCriteria.getValue().size() - 1) {
							queryBuilder.append(", ");
						}
					}
					queryBuilder.append(") ");
				}
			}
		}
		if (groupByColumnList != null) {
			queryBuilder.append("GROUP BY ");
			for (int colSeq = 0; colSeq < groupByColumnList.size(); colSeq++) {
				queryBuilder.append("TBL." + groupByColumnList.get(colSeq).trim().toUpperCase()
						+ " ");
				if (colSeq != outputColumnList.size() - 1) {
					queryBuilder.append(",");
				}
			}
		}
		System.out.println("QUERY-5 : " + queryBuilder.toString());
		Map<String, String> resultMap = null;
		Query resultSet = entityManager.createNativeQuery(queryBuilder.toString());

		// ??? - Only 1st and 2nd output column are processed as output ???
		List<Object[]> resultSetList = resultSet.getResultList();
		if (resultSetList != null & resultSetList.size() > 0) {
			resultMap = new HashMap<String, String>();
			for (Object[] e : resultSetList) {
				resultMap.put(e[0].toString(), e[1].toString());
			}
		}
		return resultMap;
		// for (Object[] e : resultSetList) {
		// System.out.println("");
		// for(int i=0;i<outputColumnList.size();i++) {
		// System.out.print(e[i]+" ||");
		// }

		// ??? - Ideally the return should be a Map(x)<String-KEY,<Map(x)>> where String-KEY will be
		// the group by column ???
		// As of now return only two rows so sending as Map<String,String>
		// ??? - Design Alternative: A List can be returned a output with column values separate
		// by ,
	}
}